﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Jinjia.Approve.Entity;
using Jinjia.Approve.Entity.Entity;
using Jinjia.Approve.Entity.Entity.Brand;
using Jinjia.Approve.IRepository.JJFlow;
using Jinjia.Approve.Model.VM.Org;
using JinJia.Core.Base.Exceptions;
using Jinjia.Core.UnitOfWork.UnitOfWorkComponent;
using MySql.Data.MySqlClient;
using SqlSugar;
using SqlSugar.Extensions;
using Jinjia.Approve.Model.VM;

namespace Jinjia.Approve.Repository.JJFlow
{
    public class JJApproversRepository : IJJApproversRepository
    {
        private readonly ISqlSugarClient _db;

        public JJApproversRepository(IUnitOfWork unitOfWork)
        {
            _db = unitOfWork.GetDbClient();
        }

        public async Task<List<brand_leader_mapping_tbl>> GetBrandLeader(string biz_order_id)
        {
            if (!int.TryParse(biz_order_id, out var master_id)) throw new BusinessException("参数有误！");
            var sql = @"SELECT c.brand_code,d.purchase_user_id from cg_supply_price_approve_detail_tbl a
                        inner join product_list_tbl b on a.goods_no=b.goods_no
                        inner join brand_tbl c on b.wdt_brand_id = c.wdt_brand_id
                        left join brand_leader_mapping_tbl d on c.brand_code = d.brand_code and d.deleted= 0 
                        where a.master_id = @master_id and a.deleted=0";
            var brandLeaderList = await _db.Ado.SqlQueryAsync<brand_leader_mapping_tbl>(sql, new { master_id });
            return brandLeaderList;
        }

        public async Task<OrgStructure> GetOrgStructure(int user_id)
        {
            var sql = @" SELECT DISTINCT e.id AS user_id,e.user_name,e.ding_user_id,s.child_node_id AS node_id
                        FROM erpa_user_tbl e
                        JOIN oa_org_member_list m ON e.ding_user_id = m.external_user_id
                        JOIN oa_org_structure_tbl s ON m.member_id = s.child_node_id
                        WHERE 1=1 AND e.id = @user_id
                       ";

            var param = new List<SugarParameter> { new SugarParameter("@user_id", user_id) };
            var result = await _db.Ado.SqlQueryAsync<OrgStructure>(sql, param);
            return result.FirstOrDefault();
        }

        public async Task<OrgStructure> GetOrgNodeByUserId(int user_id)
        {
            var sql = @" SELECT DISTINCT e.id AS user_id,e.user_name,e.ding_user_id,s.child_node_id AS node_id
                        FROM erpa_user_tbl e
                        JOIN oa_org_member_list m ON e.ding_user_id = m.external_user_id
                        JOIN oa_org_structure_tbl s ON m.member_id = s.child_node_id
                        WHERE 1=1 AND e.id = @user_id
                       ";

            var param = new List<SugarParameter> { new SugarParameter("@user_id", user_id) };
            var result = await _db.Ado.SqlQueryAsync<OrgStructure>(sql, param);
            return result.FirstOrDefault();
        }

        public async Task<List<OrgDept>> GetOrgNodeByNodeId(int node_id)
        {
            var sql = @"SELECT m.member_name AS user_name,m.external_user_id AS ding_user_id,s.node_id
                            FROM oa_org_structure_tbl s 
                            JOIN oa_org_structure_tbl s2 ON s.node_id = s2.child_node_id
                            JOIN oa_org_member_list m ON m.member_id = s2.child_node_id
                            WHERE s.child_node_id = @node_id
                       ";

            var param = new List<SugarParameter> { new SugarParameter("@node_id", node_id) };
            var result = await _db.Ado.SqlQueryAsync<OrgDept>(sql, param);
            return result;
        }

        public async Task<OrgNode> GetOrgLeaderNodeByNodeId(int node_id)
        {
            var sql =
                @"SELECT e.id AS user_id,if(e.user_name IS NULL,m.member_name,e.user_name) AS user_name,m.external_user_id AS ding_user_id,s.child_node_id AS node_id ,s.node_id AS parent_node_id,s.is_dept_leader
                            FROM oa_org_structure_tbl s 
                            JOIN oa_org_member_list m ON m.member_id = s.child_node_id
                            LEFT JOIN erpa_user_tbl e ON e.ding_user_id = m.external_user_id
                            WHERE s.node_id = @node_id AND s.is_dept_leader = 1 AND e.action_flag = '1'
                       ";

            var param = new List<SugarParameter> { new SugarParameter("@node_id", node_id) };
            var result = await _db.Ado.SqlQueryAsync<OrgNode>(sql, param);
            return result.FirstOrDefault();
        }

        public async Task<List<Model.VM.Org.Org>> FXBlackListCustomersApproverGetOrg(string biz_order_id)
        {
            //根据业务id查找到创建人对应的ding_user_id
            var sql = new StringBuilder();
            sql.Append($@"SELECT                                                                                        ");
            sql.Append($@" c.id as user_id                                                                              ");
            sql.Append($@",a.id                                                                                         ");
            sql.Append($@",a.org_code                                                                                   ");
            sql.Append($@",a.member_id                                                                                  ");
            sql.Append($@",a.member_name                                                                                ");
            sql.Append($@",a.phone_no                                                                                   ");
            sql.Append($@",a.external_user_id                                                                           ");
            sql.Append($@",a.position                                                                                   ");
            sql.Append($@",a.staff_status                                                                               ");
            sql.Append($@",a.dimission_date                                                                             ");
            sql.Append($@",b.id as oa_org_structure_id                                                                  ");
            sql.Append($@",b.node_id                                                                                    ");
            sql.Append($@",b.node_type                                                                                  ");
            sql.Append($@",b.parent_node_id                                                                             ");
            sql.Append($@",b.child_node_id                                                                              ");
            sql.Append($@",b.is_dept_leader                                                                             ");
            sql.Append($@"FROM                                                                                          ");
            sql.Append($@"	oa_org_member_list a                                                                         ");
            sql.Append($@"LEFT JOIN oa_org_structure_tbl b on a.member_id=b.child_node_id                               ");
            sql.Append($@"LEFT JOIN erpa_user_tbl c on c.ding_user_id=a.external_user_id                                ");
            sql.Append($@"	where b.node_id in                                                                           ");
            sql.Append($@"	(                                                                                            ");
            sql.Append($@"	SELECT                                                                                       ");
            sql.Append($@"    b.node_id                                                                                 ");
            sql.Append($@"   FROM                                                                                       ");
            sql.Append($@"   	oa_org_member_list a                                                                     ");
            sql.Append($@"   LEFT JOIN oa_org_structure_tbl b on a.member_id=b.child_node_id and b.org_code='DingTalk'  ");
            sql.Append($@"	 LEFT JOIN erpa_user_tbl c on c.ding_user_id=a.external_user_id                              ");
            sql.Append($@"	 LEFT JOIN fx_blacklist_tbl d on d.create_user_id=c.id                                          ");
            sql.Append($@"   where                                                                                      ");
            sql.Append($@"	  d.id='{biz_order_id}' and a.org_code='DingTalk'                                               ");
            sql.Append($@"	) and b.is_dept_leader=1;                                                                    ");
            var org_list = await _db.Ado.SqlQueryAsync<Model.VM.Org.Org>(sql.ToString());
            return org_list;
        }

        public async Task<List<Model.VM.Org.Org>> FXPlatformCustomersApproverGetOrg(string biz_order_id)
        {
            //根据业务id查找到创建人对应的ding_user_id
            StringBuilder sql = new StringBuilder();
            sql.Append($@"SELECT                                                                                        ");
            sql.Append($@" c.id as user_id                                                                              ");
            sql.Append($@",a.id                                                                                         ");
            sql.Append($@",a.org_code                                                                                   ");
            sql.Append($@",a.member_id                                                                                  ");
            sql.Append($@",a.member_name                                                                                ");
            sql.Append($@",a.phone_no                                                                                   ");
            sql.Append($@",a.external_user_id                                                                           ");
            sql.Append($@",a.position                                                                                   ");
            sql.Append($@",a.staff_status                                                                               ");
            sql.Append($@",a.dimission_date                                                                             ");
            sql.Append($@",b.id as oa_org_structure_id                                                                  ");
            sql.Append($@",b.node_id                                                                                    ");
            sql.Append($@",b.node_type                                                                                  ");
            sql.Append($@",b.parent_node_id                                                                             ");
            sql.Append($@",b.child_node_id                                                                              ");
            sql.Append($@",b.is_dept_leader                                                                             ");
            sql.Append($@"FROM                                                                                          ");
            sql.Append($@"	oa_org_member_list a                                                                         ");
            sql.Append($@"LEFT JOIN oa_org_structure_tbl b on a.member_id=b.child_node_id                               ");
            sql.Append($@"LEFT JOIN erpa_user_tbl c on c.ding_user_id=a.external_user_id                                ");
            sql.Append($@"	where b.node_id in                                                                           ");
            sql.Append($@"	(                                                                                            ");
            sql.Append($@"	SELECT                                                                                       ");
            sql.Append($@"    b.node_id                                                                                 ");
            sql.Append($@"   FROM                                                                                       ");
            sql.Append($@"   	oa_org_member_list a                                                                     ");
            sql.Append($@"   LEFT JOIN oa_org_structure_tbl b on a.member_id=b.child_node_id and b.org_code='DingTalk'  ");
            sql.Append($@"	 LEFT JOIN erpa_user_tbl c on c.ding_user_id=a.external_user_id                              ");
            sql.Append($@"	 LEFT JOIN fx_seller_tbl d on d.create_user_id=c.id                                          ");
            sql.Append($@"   where                                                                                      ");
            sql.Append($@"	  d.business_record_id='{biz_order_id}' and a.org_code='DingTalk'                                               ");
            sql.Append($@"	) and b.is_dept_leader=1;                                                                    ");
            var org_list = await _db.Ado.SqlQueryAsync<Model.VM.Org.Org>(sql.ToString());
            return org_list;
        }

        public async Task<List<oa_org_member_list>> GetOrgMemberList(string biz_order_id)
        {
            if (!int.TryParse(biz_order_id, out int master_id)) throw new BusinessException("参数有误！");
            var sql = $@"SELECT DISTINCT b.external_user_id,member_name from goods_sample_trades_tbl a inner join oa_org_member_list b
								on FIND_IN_SET(b.external_user_id,a.auditor)>0
								where a.id ={master_id}";
            var member_list = await _db.Ado.SqlQueryAsync<oa_org_member_list>(sql);
            return member_list;
        }

        public async Task<List<int>> OakLogisticsPriceApproverGetOrg(string biz_order_id)
        {
            if (string.IsNullOrWhiteSpace(biz_order_id)) throw new BusinessException("获取审批人参数有误！");

            //根据业务id查找到创建人对应的ding_user_id
            var sql = new StringBuilder(@"SELECT 
CONVERT( b.`name`, SIGNED ) AS user_id
FROM oak_logistics_price_config_draft_tbl a 
LEFT JOIN erpa_base_tbl b on a.request_type=b.memo and b.code_type = 'oak_logistics_price_user' and b.parent_code = 'settlement_object_value' 
WHERE a.approval_id =@biz_order_id
GROUP BY b.`name`  ");
            var param = new List<SugarParameter> { new SugarParameter("@biz_order_id", biz_order_id) };
            var orgList = await _db.Ado.SqlQueryAsync<int>(sql.ToString(), param);
            return orgList;
        }

        public async Task<List<int>> GetLeaderList(int id)
        {
            //根据申请单id 获取申请单明细商品
            var purchase_goodlist = await _db.Queryable<cg_planned_order_details_tbl>().Where(x => x.master_id == id && x.deleted == 0).Select(x => x.spec_no).ToListAsync();
            //根据商品获取对应WDT品牌ID
            var brandIDlist = await _db.Queryable<product_list_tbl>().Where(x => purchase_goodlist.Contains(x.goods_no)).Select(x => x.wdt_brand_id).ToListAsync();
            //根据wdt品牌ID 获取品牌code
            var brandlist = await _db.Queryable<brand_tbl>().Where(x => brandIDlist.Contains(x.wdt_brand_id)).Select(x => x.brand_code).ToListAsync();
            //根据品牌获取对应负责人
            var leader_idlist = (await _db.Queryable<brand_leader_mapping_tbl>().Where(a => brandlist.Contains(a.brand_code) && a.deleted == 0).ToListAsync()).Select(a => a.leader_id).ToList();
            return leader_idlist;
        }

        public async Task<List<int>> PromotionApplyApproverRealizeGetApproverIds(int master_id)
        {
            return (await _db.Queryable<promotion_apply_approver_tbl>().Where(a => a.master_id == master_id && a.deleted == 0).ToListAsync()).Select(a => a.approver_id).ToList();
        }

        public async Task<List<int>> PurchaseApproverRealizeGetLeaderIdList(string biz_order_id)
        {
            if (!int.TryParse(biz_order_id, out int id)) throw new BusinessException("参数有误！");
            //根据ID获取采购单号
            var purchaseNo = (await _db.Queryable<commission_account_tbl>().Where(x => x.id == id).FirstAsync()).purchase_no;
            //根据采购单号 获取采购订单明细商品
            var purchase_goodlist = await _db.Queryable<commission_account_detail_tbl>().Where(x => x.purchase_no == purchaseNo).Select(x => x.spec_no).ToListAsync();
            //根据商品获取对应WDT品牌ID
            var brandIDlist = await _db.Queryable<product_list_tbl>().Where(x => purchase_goodlist.Contains(x.goods_no)).Select(x => x.wdt_brand_id).ToListAsync();
            //根据wdt品牌ID 获取品牌code
            var brandlist = await _db.Queryable<brand_tbl>().Where(x => brandIDlist.Contains(x.wdt_brand_id)).Select(x => x.brand_code).ToListAsync();
            //根据品牌获取对应负责人
            var leader_idlist = (await _db.Queryable<brand_leader_mapping_tbl>().Where(a => brandlist.Contains(a.brand_code) && a.deleted == 0).ToListAsync()).Select(a => a.leader_id).ToList();
            return leader_idlist;
        }

        public async Task<int> UserDimissionHandoverApproverGetUserId(string biz_order_id)
        {
            var o = await _db.Ado.GetScalarAsync($"SELECT dimission_user_id FROM user_dimission_handover_tbl where id={biz_order_id}");
            return o.ObjToInt();
        }

        public AdSettlementVM GetAdSettlementData(int master_id)
        {
            var settlement_model = _db.Ado.SqlQuery<AdSettlementVM>($"SELECT id,hr_id FROM ad_settlement_tbl WHERE id = {master_id}").FirstOrDefault();

            return settlement_model;
        }

        public List<SettlementHrDirectorVM> GetSettlementHrDirectorDatas(int hr_id)
        {
            var settlement_models = _db.Ado.SqlQuery<SettlementHrDirectorVM>($"SELECT hr_id,director_id FROM ad_settlement_hr_director_mapping_tbl WHERE hr_id = {hr_id}");

            return settlement_models;
        }

        public int GetAdAcceptanceTransactUserId(int master_id)
        {
            string sql = @$"SELECT apt.transact_user_id as transact_user_id FROM ad_acceptance_tbl aat
                            LEFT JOIN ad_finance_detail_tbl afdt ON aat.ad_finance_id = afdt.id AND afdt.deleted = 0
                            LEFT JOIN ad_promote_tbl apt ON afdt.ad_promote_id = apt.id AND apt.deleted = 0
                            WHERE aat.id = {master_id}";

            var model = _db.Ado.SqlQuery<GetAdAcceptanceTransactUserIdVM>(sql).FirstOrDefault();

            return model?.transact_user_id ?? 0;
        }
    }
}